<?php

/**
 * Step 1: Require the Slim Framework
 *
 * If you are not using Composer, you need to require the
 * Slim Framework and register its PSR-0 autoloader.
 *
 * If you are using Composer, you can skip this step.
 */
require 'Slim/Slim.php';
require 'SD/utils.php';
require 'SD/authentication.php';

\Slim\Slim::registerAutoloader();

/**
 * Step 2: Instantiate a Slim application
 *
 * This example instantiates a Slim application using
 * its default settings. However, you will usually configure
 * your Slim application now by passing an associative array
 * of setting names and values into the application constructor.
 */
$app = new \Slim\Slim();

/**
 * Step 3: Define the Slim application routes
 *
 * Here we define several Slim application routes that respond
 * to appropriate HTTP request methods. In this example, the second
 * argument for `Slim::get`, `Slim::post`, `Slim::put`, `Slim::patch`, and `Slim::delete`
 * is an anonymous function.
 */
// GET route
$app->get(
        '/', function () {
    $template = <<<EOT
<!DOCTYPE html>
    <html>
        <head>
            <meta charset="utf-8"/>
            <title>Simply Decide API Module</title>
            <style>
                html,body,div,span,object,iframe,
                h1,h2,h3,h4,h5,h6,p,blockquote,pre,
                abbr,address,cite,code,
                del,dfn,em,img,ins,kbd,q,samp,
                small,strong,sub,sup,var,
                b,i,
                dl,dt,dd,ol,ul,li,
                fieldset,form,label,legend,
                table,caption,tbody,tfoot,thead,tr,th,td,
                article,aside,canvas,details,figcaption,figure,
                footer,header,hgroup,menu,nav,section,summary,
                time,mark,audio,video{margin:0;padding:0;border:0;outline:0;font-size:100%;vertical-align:baseline;background:transparent;}
                body{line-height:1;}
                article,aside,details,figcaption,figure,
                footer,header,hgroup,menu,nav,section{display:block;}
                nav ul{list-style:none;}
                blockquote,q{quotes:none;}
                blockquote:before,blockquote:after,
                q:before,q:after{content:'';content:none;}
                a{margin:0;padding:0;font-size:100%;vertical-align:baseline;background:transparent;}
                ins{background-color:#ff9;color:#000;text-decoration:none;}
                mark{background-color:#ff9;color:#000;font-style:italic;font-weight:bold;}
                del{text-decoration:line-through;}
                abbr[title],dfn[title]{border-bottom:1px dotted;cursor:help;}
                table{border-collapse:collapse;border-spacing:0;}
                hr{display:block;height:1px;border:0;border-top:1px solid #cccccc;margin:1em 0;padding:0;}
                input,select{vertical-align:middle;}
                html{ background: #EDEDED; height: 100%; }
                body{background:#FFF;margin:0 auto;min-height:100%;padding:0 30px;width:440px;color:#666;font:14px/23px Arial,Verdana,sans-serif;}
                h1,h2,h3,p,ul,ol,form,section{margin:0 0 20px 0;}
                h1{color:#333;font-size:20px;}
                h2,h3{color:#333;font-size:14px;}
                h3{margin:0;font-size:12px;font-weight:bold;}
                ul,ol{list-style-position:inside;color:#999;}
                ul{list-style-type:square;}
                code,kbd{background:#EEE;border:1px solid #DDD;border:1px solid #DDD;border-radius:4px;-moz-border-radius:4px;-webkit-border-radius:4px;padding:0 4px;color:#666;font-size:12px;}
                pre{background:#EEE;border:1px solid #DDD;border-radius:4px;-moz-border-radius:4px;-webkit-border-radius:4px;padding:5px 10px;color:#666;font-size:12px;}
                pre code{background:transparent;border:none;padding:0;}
                a{color:#70a23e;}
                header{padding: 30px 0;text-align:center;}
            </style>
        </head>
        <body>
            <header>
                <a href="http://www.slimframework.com"><img src="" alt="Slim"/></a>
				<br>
				<a href="http://www.simplydecide.com">SimplyDecide</a>
            </header>
            <h1>Welcome to Slim!</h1>
            <p>
                Congratulations! Your Slim application is running. If this is
                your first time using Slim, start with this <a href="http://www.slimframework.com/learn" target="_blank">"Hello World" Tutorial</a>.
            </p>
            <section>
                <h2>Get Started</h2>
                <ol>
                    <li>The application code is in <code>index.php</code></li>
                    <li>Read the <a href="http://docs.slimframework.com/" target="_blank">online documentation</a></li>
                    <li>Follow <a href="http://www.twitter.com/slimphp" target="_blank">@slimphp</a> on Twitter</li>
                </ol>
            </section>
            <section>
                <h2>Slim Framework Community</h2>

                <h3>Support Forum and Knowledge Base</h3>
                <p>
                    Visit the <a href="http://help.slimframework.com" target="_blank">Slim support forum and knowledge base</a>
                    to read announcements, chat with fellow Slim users, ask questions, help others, or show off your cool
                    Slim Framework apps.
                </p>

                <h3>Twitter</h3>
                <p>
                    Follow <a href="http://www.twitter.com/slimphp" target="_blank">@slimphp</a> on Twitter to receive the very latest news
                    and updates about the framework.
                </p>
            </section>
            <section style="padding-bottom: 20px">
                <h2>Slim Framework Extras</h2>
                <p>
                    Custom View classes for Smarty, Twig, Mustache, and other template
                    frameworks are available online in a separate repository.
                </p>
                <p><a href="https://github.com/codeguy/Slim-Extras" target="_blank">Browse the Extras Repository</a></p>
            </section>
        </body>
    </html>
EOT;
    echo $template;
}
);

$app->get('/hello/:name', function ($name) {
    echo "Hello, $name";
});

$app->get('/menu', function () {
    $link = getDbLink();
    $query = "select t1.category_id as id, t1.name  as name, t2.category_count as count from oc_category_description as t1 , (select category_id,count(*) as category_count from oc_question group by category_id) as t2 where t1.category_id = t2.category_id";
    $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
    $records = array();
    if (mysql_num_rows($result)) {
        while ($record = mysql_fetch_assoc($result)) {
            $records[] = array('results' => $record);
        }
    }
    header('Content-type: application/json');
    echo json_encode(array('records' => $records));
    closeDbLink($link);
});


$app->get('/decisions', function () {
    $link = getDbLink();
    $query = "select t1.question_id, t2.question, count(*) as question_count from oc_voting as t1, oc_question as t2 where t1.question_id in (select oc_decision.question_id from oc_decision,oc_question where oc_question.who_can_see = 0 and oc_decision.question_id = oc_question.question_id) and t1.question_id = t2.question_id group by question_id order by question_count DESC LIMIT 5";
    $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
    $records = array();
    if (mysql_num_rows($result)) {
        while ($record = mysql_fetch_assoc($result)) {
            $records[] = array('results' => $record);
        }
    }
    header('Content-type: application/json');
    echo json_encode(array('records' => $records));
    closeDbLink($link);
});

$app->get('/decisions', function () {
    $link = getDbLink();
    $query = "select t1.question_id, t2.question, count(*) as question_count from oc_voting as t1, oc_question as t2 where t1.question_id in (select oc_decision.question_id from oc_decision,oc_question where oc_question.who_can_see = 0 and oc_decision.question_id = oc_question.question_id) and t1.question_id = t2.question_id group by question_id order by question_count DESC LIMIT 5";
    $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
    $records = array();
    if (mysql_num_rows($result)) {
        while ($record = mysql_fetch_assoc($result)) {
            $records[] = array('results' => $record);
        }
    }
    header('Content-type: application/json');
    echo json_encode(array('records' => $records));
    closeDbLink($link);
});

$app->get('/decisions/pending', function () {
    session_start();
    if (!empty($_SESSION['user_id'])) {
        $user = intval($_SESSION['user_id']);
        $link = getDbLink();
        $query = "SELECT DISTINCT `question_id` FROM  `oc_voting` WHERE  `voter_id` = " . $user . " AND question_id NOT IN (select question_id from oc_decision)";
        $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
        $records = array();
        if (mysql_num_rows($result)) {
            while ($record = mysql_fetch_assoc($result)) {
                $records[] = getQuestionDetails($record['question_id'], $link);
            }
        }
        header('Content-type: application/json');
        echo json_encode(array('questions' => $records));
        closeDbLink($link);
    } else {
        echo json_encode(array('userid' => -1, 'message' => 'Please Login'));
    }
});




$app->get('/list/:list', function ($list) {
    if ($list == 'country') {
        $link = getDbLink();
        $query = "select `country_id`, `name` from `oc_country`";
        $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
        $records = array();
        if (mysql_num_rows($result)) {
            while ($record = mysql_fetch_assoc($result)) {
                $records[] = array('results' => $record);
            }
        }
        header('Content-type: application/json');
        echo json_encode(array('records' => $records));
        closeDbLink($link);
    } else if ($list == 'region') {
        $link = getDbLink();
        $query = 'select * from `oc_zone`';
        $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
        $records = array();
        if (mysql_num_rows($result)) {
            while ($record = mysql_fetch_assoc($result)) {
                //var_dump(array('record'=>$record));
                $records[] = array('record' => $record);
            }
        } else {
            $record = array(
                "zone_id" => 0,
                "name" => "--",
            );
            $records[] = array('record' => $record);
        }
        header('Content-type: application/json');
        //var_dump(json_encode(array('results'=>$records)));
        //echo json_last_error();
        echo json_encode(array('results' => $records));
        closeDbLink($link);
    } else {
        echo 'unknown header';
    }
});

$app->get('/homepage', function () {
    $link = getDbLink();
    $counts = array('decisions' => getCount("oc_decision", $link),
        'votes' => getCount("oc_voting", $link),
        'questions' => getCount("oc_question", $link));
    $recentTopics = getTopTopics($link);
    $carouselData = array();
    for ($i = 0; $i < 3; $i++) {
        $carouselData[] = questionDetails($recentTopics[$i]['question_id'], $link);
    }
    header('Content-type: application/json');
    echo json_encode(array('counts' => $counts,
        'decisions' => getTopDecisions($link),
        'topics' => $recentTopics,
        'carouselData' => $carouselData));
    closeDbLink($link);
});

$app->get('/logout', function () {
    session_start();
    session_destroy();
});

$app->get('/authenticate/user', function () {
    session_start();
    if (!empty($_SESSION['user_id'])) {
        $user = intval($_SESSION['user_id']);
        $link = getDbLink();
        $query = "SELECT `email`,`firstname`,`lastname` FROM  `oc_customer` WHERE `customer_id`='" . $user . "'";
        $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
        if (mysql_num_rows($result)) {
            while ($record = mysql_fetch_assoc($result)) {
                $email = $record['email'];
                $first_name = $record['firstname'];
                $last_name = $record['lastname'];
            }
        }
        closeDbLink($link);
        echo json_encode(array('userid' => $_SESSION['user_id'], 'email' => $email, 'firstName' => $first_name, 'lastName' => $last_name));
    } else {
        echo json_encode(array('userid' => -1));
    }
}
);

$app->get('/search/users/:key', function($key) {
    $link = getDbLink();
    $query = "SELECT a.customer_id,a.firstname,a.lastname,a.email," .
            "MATCH ( `firstname`, `lastname`, `email`) " .
            "AGAINST ('" . $key . "' IN BOOLEAN MODE) as `a.rel`, b.request_status, b.to_friend_id " .
            "FROM `oc_customer` as a left join oc_friends as b on a.customer_id = b.to_friend_id or a.customer_id = b.from_friend_id" .
            " WHERE MATCH ( `firstname`, `lastname`, `email`)" .
            "AGAINST ('" . $key . "' IN BOOLEAN MODE)" .
            "ORDER BY `a.rel` DESC";
    $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
    $users = array();
    if (mysql_num_rows($result)) {
        while ($record = mysql_fetch_assoc($result)) {
            $users[] = $record;
        }
    }
    header('Content-type: application/json');
    echo json_encode(array('users' => $users));
    closeDbLink($link);
});

$app->post('/friends/approve/', function() {
    $friend_id = isset($_POST['approveuserid']) ? $_POST['approveuserid'] : "";
    session_start();
    if (!empty($_SESSION['user_id'])) {
        $user = intval($_SESSION['user_id']);
        $link = getDbLink();
        $query = "SELECT `request_status` FROM `oc_friends` WHERE `to_friend_id`='" . $user . "'" .
                "AND `from_friend_id`='" . $friend_id . "' AND `request_status` = 0";
        $requests = mysql_query($query, $link) or die('Errant query:  ' . $query);
        if (mysql_num_rows($requests)) {
            /* $approve = "";
            while ($record = mysql_fetch_assoc($requests)) {
                $approve = $record['approve'];
            } */
            $updateQuery = "UPDATE `oc_friends` SET `request_status` = 1 WHERE " .
                    "`to_friend_id` = " . $user . " AND `from_friend_id` = " . $friend_id;
            $updateResult = mysql_query($updateQuery, $link)or die('Errant query:  ' . $updateQuery);
            echo json_encode(array('friend_id' => $friend_id, 'message' => 'User added as friend'));
        } else {
            echo json_encode(array('friend_id' => $friend_id, 'message' => 'No open requests OR approved/rejected already made'));
        }
        closeDbLink($link);
    } else {
        echo json_encode(array('userid' => -1, 'message' => 'Please Login'));
    }
});


$app->post('/friends/reject/', function() {
    $friend_id = isset($_POST['removeuserid']) ? $_POST['removeuserid'] : "";
    session_start();
    if (!empty($_SESSION['user_id'])) {
        $user = intval($_SESSION['user_id']);
        $link = getDbLink();
        $query = "SELECT `request_status` FROM `oc_friends` WHERE `to_friend_id`='" . $user . "'" .
                "AND `from_friend_id`='" . $friend_id . "' AND `request_status` = 0";
        $requests = mysql_query($query, $link) or die('Errant query:  ' . $query);
        if (mysql_num_rows($requests)) {
            $approve = "";
            while ($record = mysql_fetch_assoc($requests)) {
                //$approve = $record['approve'];
            }
            $updateQuery = "UPDATE `oc_friends` SET `request_status` =2 WHERE " .
                    "`to_friend_id` = " . $user . " AND `from_friend_id` = " . $friend_id;
            $updateResult = mysql_query($updateQuery, $link)or die('Errant query:  ' . $updateQuery);
            echo json_encode(array('friend_id' => $friend_id, 'message' => 'You  rejected to be a friend to'.$friend_id ));
        } else {
            echo json_encode(array('friend_id' => $friend_id, 'message' => 'No open requests OR approved/rejected already made'));
        }
        closeDbLink($link);
    } else {
        echo json_encode(array('userid' => -1, 'message' => 'Please Login'));
    }
});

$app->post('/friends/add/', function() {
    $friend_id = isset($_POST['adduserid']) ? $_POST['adduserid'] : "";
    session_start();
    if (!empty($_SESSION['user_id'])) {
        $user = intval($_SESSION['user_id']);
        $link = getDbLink();
        $query = "select `customer_id`,`social_site` from `oc_customer` where`customer_id`='" . $friend_id . "'";
        $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
        if (mysql_num_rows($result)) {
            while ($record = mysql_fetch_assoc($result)) {
                $social_site = $record['social_site'];
            }
            
            /* need to filter this in the search */
            /*$query2 = "SELECT `request_status`, `approve` FROM `oc_friend_circle` WHERE `from_friend_id`='" . $user . "'" .
                    "AND `to_friend_id`='" . $friend_id . "'";
            $requests = mysql_query($query2, $link) or die('Errant query:  ' . $query2);
            if (mysql_num_rows($requests)) {
                $request_status = "";
                $approve = "";
                while ($record = mysql_fetch_assoc($requests)) {
                    $request_status = $record['request_status'];
                    $approve = $record['approve'];
                }
                echo json_encode(array('friend_id' => $friend_id, 'request_status' => $request_status, 'approve' => $approve, 'message' => 'Request Already Made'));
            } else {*/
                $insertQuery = "INSERT INTO `oc_friends`(`from_friend_id`, `to_friend_id`, `request_status`, `friend_type`, `is_unfriend`) VALUES (" .
                        $user . "," . $friend_id . ",0,'" . $social_site . "',0)";
                $insertResult = mysql_query($insertQuery, $link)or die('Errant query:  ' . $insertQuery);
                echo json_encode(array('friend_id' => $friend_id, 'social_site' => $social_site, 'message' => 'Request Made'));
            //}
        } else {
            echo json_encode(array('friend_id' => $friend_id, 'message' => 'Invalid id'));
        }
        closeDbLink($link);
    } else {
        echo json_encode(array('userid' => -1, 'message' => 'Please Login'));
    }
});
$app->post('/friends/unfriend/', function() {
    $friend_id = isset($_POST['unfrienduserid']) ? $_POST['unfrienduserid'] : "";
    session_start();
    if (!empty($_SESSION['user_id'])) {
        $user = intval($_SESSION['user_id']);
        $link = getDbLink();
        $query = "SELECT `request_status` FROM `oc_friends` WHERE `to_friend_id`='" . $user . "'" .
                "AND `from_friend_id`='" . $friend_id . "' AND `request_status` = 0";
        $requests = mysql_query($query, $link) or die('Errant query:  ' . $query);
        if (mysql_num_rows($requests)) {
            /* $approve = "";
            while ($record = mysql_fetch_assoc($requests)) {
                $approve = $record['approve'];
            } */
            $updateQuery = "UPDATE `oc_friends` SET `request_status` = 1 WHERE " .
                    "`to_friend_id` = " . $user . " AND `from_friend_id` = " . $friend_id;
            $updateResult = mysql_query($updateQuery, $link)or die('Errant query:  ' . $updateQuery);
            echo json_encode(array('friend_id' => $friend_id, 'message' => 'User added as friend'));
        } else {
            echo json_encode(array('friend_id' => $friend_id, 'message' => 'No open requests OR approved/rejected already made'));
        }
        closeDbLink($link);
    } else {
        echo json_encode(array('userid' => -1, 'message' => 'Please Login'));
    }
});
// POST route
$app->post('/authenticate', function () {
//            console.log(here);
    $username = isset($_POST['usr']) ? $_POST['usr'] : 0;
    $password = isset($_POST['pwd']) ? $_POST['pwd'] : 0;
//            echo $username;
    login($username, $password);
}
);

$app->post('/signup', function() {
    $link = getDbLink();
    $login = isset($_POST['login']) ? $_POST['login'] : "";
    $query = "select `customer_id` from `oc_customer` where`email`='" . $login . "'";
    $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
    if (mysql_num_rows($result)) {
        echo json_encode(array('result' => 'failure', 'message' => 'Email id already exists.'));
        return;
    }
    $password = isset($_POST['password']) ? $_POST['password'] : "";
    $firstname = isset($_POST['firstname']) ? $_POST['firstname'] : "";
    $lastname = isset($_POST['lastname']) ? $_POST['lastname'] : "";
    $gender = isset($_POST['gender']) ? $_POST['gender'] : "";
    $countrycode = isset($_POST['countrycode']) ? $_POST['countrycode'] : 0;
    $regioncode = isset($_POST['regioncode']) ? $_POST['regioncode'] : 0;

    try {
        begin($link);
        $salt = substr(md5(uniqid(rand(), true)), 0, 9);
        $encodedpassword = sha1($salt . sha1($salt . sha1($password)));
        $query1 = "insert into `oc_customer` (`firstname`, `lastname`, `gender`,`email`,`password`, `salt`,`date_added`) values ('" . $firstname . "', '" . $lastname . "', '" . $gender . "','" . $login . "','" . $encodedpassword . "','" . $salt . "',now());";
        $result = mysql_query($query1, $link);
        $query3 = "SELECT `customer_id` FROM  `oc_customer` WHERE `email`='" . $login . "'";
        $result = mysql_query($query3, $link);
        $resultarray = mysql_fetch_assoc($result);
        $assignedId = $resultarray['customer_id'];
        $query2 = "insert into `oc_address` (`customer_id`,`firstname`, `lastname`,`country_id`,`zone_id`) values ('" . $assignedId . "','" . $firstname . "', '" . $lastname . "', " . intval($countrycode) . "," . intval($regioncode) . ")";
        //echo $query2;
        $result = mysql_query($query2, $link);
        commit($link);
        echo json_encode(array('result' => 'success', "id" => $assignedId, "email" => $login, "firstName" => $firstname, "lastName" => lastName));
    } catch (Exception $e) {
        rollback($link);
        echo json_encode(array('result' => 'failure'));
    }
    closeDbLink($link);
});

$app->get('/search/:keys', function ($keys) {
    $link = getDbLink();
    $keyword_tokens = explode(',', $keys);
    $query = "select t1.question_id,t1.question,t1.customer_id,t1.hide_my_name,t2.answer_id,t2.answer FROM (SELECT `question`,`question_id`,`customer_id`,`hide_my_name` FROM `oc_question` WHERE `question` LIKE'%" . implode("%' or `question` LIKE '%", $keyword_tokens) . "%'" . ") AS t1 INNER JOIN oc_answer AS t2 ON t1.question_id = t2.question_id";
    $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
    $records = array();
    if (mysql_num_rows($result)) {
        while ($record = mysql_fetch_assoc($result)) {
            //var_dump($record);
            $records[] = array('record' => $record);
        }
    }
    header('Content-type: application/json');
    echo json_encode(array('records' => $records));
    closeDbLink($link);
});

$app->get('/dashboard', function() {
    session_start();
    if (!empty($_SESSION['user_id'])) {
        $user = intval($_SESSION['user_id']);
        $link = getDbLink();
        $decisions = getDecisionCount($user, $link);
        $user_data = fetchUserData($user, $link);
        $user_posts = fetchUserQuestions($user, $link);

        $query = "SELECT `customer_id`, `firstname`, `lastname` FROM `oc_customer` WHERE `customer_id` IN (SELECT `from_friend_id` from `oc_friend_circle` WHERE `to_friend_id` = " .
                $user . " AND `request_status` = 1)";
        $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
        $pending_requests = array();
        if (mysql_num_rows($result)) {
            while ($record = mysql_fetch_assoc($result)) {
                $pending_requests[] = $record;
            }
        }
        header('Content-type: application/json');
        echo json_encode(array('result' => 'success',
            'decisions' => $decisions,
            'profile' => $user_data,
            "posts" => $user_posts,
            "pending_requests" => $pending_requests));
        closeDbLink($link);
    } else {
        echo json_encode(array('userid' => -1, 'message' => 'Please Login'));
    }
});

//get friends list
$app->get('/friends/', function() {
    session_start();
    $user = intval($_SESSION['user_id']);
    $link = getDbLink();
    $query = "SELECT `customer_id`, `firstname`, `lastname` FROM `oc_customer` WHERE `customer_id` IN (SELECT `from_friend_id` from `oc_friends` WHERE `to_friend_id` = " .
                $user . " or from_friend_id = ". $user ." AND `request_status` = 1)";
    $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
    $records = array();
    if (mysql_num_rows($result)) {
        while ($record = mysql_fetch_assoc($result)) {
            //var_dump($record);
            $records[] = array('record' => $record);
        }
    }
    header('Content-type: application/json');
    echo json_encode(array('records' => $records));
    closeDbLink($link);
});

$app->get('/question/:id', function($questionid) {
    $link = getDbLink();
    $query1 = "SELECT `question_id`, `question` , `recent_activity_date`,`customer_id`, `category_id` FROM `oc_question` WHERE `question_id` = " . intval($questionid);
    $question_info = mysql_query($query1, $link) or die('Errant query:  ' . $query1);
    $question_info = mysql_fetch_array($question_info);
    $query = "SELECT a1.answer_id,a1.answer, COUNT(a2.answer_id)AS votes " .
            "FROM (SELECT answer_id, answer from  `oc_answer` WHERE question_id = " . intval($questionid) .
            ")  AS a1 LEFT JOIN (SELECT `vote_id`,`answer_id` FROM `oc_voting` WHERE `question_id` = " . intval($questionid) .
            ") AS a2 on a1.answer_id = a2.answer_id GROUP BY a1.answer_id";
    $answers = mysql_query($query, $link) or die('Errant query:  ' . $query);
    if (mysql_num_rows($answers)) {
        while ($record = mysql_fetch_assoc($answers)) {
            $records[] = $record;
        }
    }
    $queryCustomer = "SELECT `firstname`,`lastname` FROM  `oc_customer` WHERE `customer_id`=" . $question_info['customer_id'];
    $customerDetails = mysql_query($queryCustomer, $link) or die('Errant query:  ' . $queryCustomer);
    $customer_first_name = "";
    $customer_last_name = "";

    if (mysql_num_rows($customerDetails)) {
        while ($record = mysql_fetch_assoc($customerDetails)) {
            $customer_first_name = $record['firstname'];
            $customer_last_name = $record['lastname'];
        }
    }

    $queryCategory = "SELECT `name` FROM `oc_category_description` WHERE `category_id` =" . $question_info['category_id'];
    $categoryDetails = mysql_query($queryCategory, $link) or die('Errant query:  ' . $queryCategory);
    $category_name = "";

    if (mysql_num_rows($customerDetails)) {
        while ($record = mysql_fetch_assoc($categoryDetails)) {
            $category_name = $record['name'];
        }
    }
    $decision = getDecision($questionid, $link);
    header('Content-type: application/json');
    echo json_encode(array('question_id' => $question_info['question_id'],
        'question' => $question_info['question'],
        'category' => array('id' => $question_info['category_id'],
            'name' => $category_name),
        'postedBy' => array('id' => $question_info['customer_id'],
            'firstName' => $customer_first_name,
            'lastName' => $customer_last_name),
        'postedOn' => $question_info['recent_activity_date'],
        'answers' => $records,
        'decision' => $decision));
    closeDbLink($link);
});

$app->get('/category/:id', function($categoryId) {
    $link = getDbLink();
    $query = "select question_id,question from oc_question where who_can_see = 0 and category_id=" . intval($categoryId) . " order by recent_activity_date DESC LIMIT 10";
    $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
    $records = array();
    if (mysql_num_rows($result)) {
        while ($record = mysql_fetch_assoc($result)) {
            //var_dump($record);
            $records[] = array('record' => $record);
        }
    }
    header('Content-type: application/json');
    echo json_encode(array('records' => $records));
    closeDbLink($link);
});


$app->get('/question/category/:id', function($categoryid) {
    $link = getDbLink();
    $query = "select t1.question_id,t1.question,t1.customer_id,t1.hide_my_name,t2.answer_id,t2.answer FROM (SELECT `question`,`question_id`,`customer_id`,`hide_my_name` FROM `oc_question` WHERE `category_id` = " . intval($categoryid) . ") AS t1 INNER JOIN oc_answer AS t2 ON t1.question_id = t2.question_id";
    $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
    $records = array();
    if (mysql_num_rows($result)) {
        while ($record = mysql_fetch_assoc($result)) {
            //var_dump($record);
            $records[] = array('record' => $record);
        }
    }
    header('Content-type: application/json');
    echo json_encode(array('records' => $records));
    closeDbLink($link);
});

$app->get('/category/:id/top', function($categoryid) {
    $link = getDbLink();
    $query = "select question_id from oc_question where who_can_see = 0 AND `category_id` = " . intval($categoryid) . " order by recent_activity_date DESC LIMIT 5";
    $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
    $records = array();
    if (mysql_num_rows($result)) {
        while ($record = mysql_fetch_assoc($result)) {
            $records[] = getQuestionDetails($record['question_id'], $link);
        }
    }
    header('Content-type: application/json');
    echo json_encode(array('questions' => $records));
    closeDbLink($link);
});

$app->post('/decide', function() {
    $link = getDbLink();
    $questionId = isset($_POST['questionid']) ? $_POST['questionid'] : "";
    $answerId = isset($_POST['answerid']) ? $_POST['answerid'] : "";
    $query = "select * from oc_decision where question_id = " . intval($questionId);
    $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
    $records = array();
    if (mysql_num_rows($result)) {
        echo json_encode(array('result' => 'failure', 'answer_id' => $answerId, 'message' => 'Decision already made.'));
    } else {
        $SQL = " INSERT INTO oc_decision SET ";
        $SQL.= " question_id = '" . intval($questionId) . "',";
        $SQL.= " answer_id = '" . intval($answerId) . "'";
        mysql_query($SQL, $link) or die('Errant query:  ' . $SQL);
        echo json_encode(array('result' => 'success', 'answer_id' => $answerId, 'message' => 'Decided.'));
    }
});

$app->post('/vote', function() {
    $link = getDbLink();
    $login = isset($_POST['login']) ? $_POST['login'] : 0;
    $questionVoterId = isset($_POST['questionvoterid']) ? $_POST['questionvoterid'] : "";
    $questionId = isset($_POST['questionid']) ? $_POST['questionid'] : "";
    $answerId = isset($_POST['answerid']) ? $_POST['answerid'] : "";

    $query = "select `check_vote` from `oc_voting` where`voter_id`='" . $login . "' and `question_id`='" . $questionId . "'";
    $result = mysql_query($query, $link) or die('Errant query:  ' . $query);

    if (mysql_num_rows($result)) {
        $resultarray = mysql_fetch_assoc($result);
        $checkVote = $resultarray['check_vote'];
        $votesBalance = 4 - intval($checkVote);
        if ($votesBalance > 0) {

            $SQL = " UPDATE oc_voting SET ";
            $SQL.= " answer_id = '" . $answerId . "',";
            $SQL.= " check_vote = '" . ++$checkVote . "',";
            $SQL.= " vote_date = NOW() ";
            $SQL.= " WHERE voter_id = '" . intval($login) . "'";
            $SQL.= " AND question_id = '" . intval($questionId) . "'";

            mysql_query($SQL, $link) or die('Errant query:  ' . $SQL);

            $SQL = " UPDATE oc_question SET ";
            $SQL.= " recent_activity_date = NOW()";
            $SQL.= " WHERE question_id = '" . $questionId . "'";

            mysql_query($SQL, $link) or die('Errant query:  ' . $SQL);
            $queryAnswers = "SELECT a1.answer_id,a1.answer, COUNT(a2.answer_id)AS votes " .
                    "FROM (SELECT answer_id, answer from  `oc_answer` WHERE question_id = " . intval($questionId) .
                    ")  AS a1 LEFT JOIN (SELECT `vote_id`,`answer_id` FROM `oc_voting` WHERE `question_id` = " . intval($questionId) .
                    ") AS a2 on a1.answer_id = a2.answer_id GROUP BY a1.answer_id";
            $answers = mysql_query($queryAnswers, $link) or die('Errant query:  ' . $queryAnswers);
            if (mysql_num_rows($answers)) {
                while ($record = mysql_fetch_assoc($answers)) {
                    $records[] = $record;
                }
            }

            echo json_encode(array('result' => 'success', 'answer_id' => $answerId, 'answers' => $records, 'remainingvotes' => $votesBalance - 1, 'message' => 'vote updated'));
        } else {
            $queryAnswers = "SELECT a1.answer_id,a1.answer, COUNT(a2.answer_id)AS votes " .
                    "FROM (SELECT answer_id, answer from  `oc_answer` WHERE question_id = " . intval($questionId) .
                    ")  AS a1 LEFT JOIN (SELECT `vote_id`,`answer_id` FROM `oc_voting` WHERE `question_id` = " . intval($questionId) .
                    ") AS a2 on a1.answer_id = a2.answer_id GROUP BY a1.answer_id";
            $answers = mysql_query($queryAnswers, $link) or die('Errant query:  ' . $queryAnswers);
            if (mysql_num_rows($answers)) {
                while ($record = mysql_fetch_assoc($answers)) {
                    $records[] = $record;
                }
            }
            echo json_encode(array('result' => 'failure', 'answer_id' => $answerId, 'answers' => $records, 'remainingvotes' => $votesBalance - 1, 'message' => 'max votes allowed on a question is 4'));
        }
    } else {
        //add
        $SQL = " INSERT INTO oc_voting SET ";
        $SQL.= " voter_id = '" . intval($login) . "',";
        $SQL.= " question_user_id = '" . intval($questionVoterId) . "',";
        $SQL.= " question_id = '" . intval($questionId) . "',";
        $SQL.= " answer_id = '" . intval($answerId) . "',";
        $SQL.= " check_vote = '1',";
        $SQL.= " vote_date = NOW() ";

        mysql_query($SQL, $link) or die('Errant query:  ' . $SQL);

        $SQL = " UPDATE oc_question SET ";
        $SQL.= " recent_activity_date = NOW()";
        $SQL.= " WHERE question_id = '" . $questionId . "'";

        mysql_query($SQL, $link) or die('Errant query:  ' . $SQL);
        $queryAnswers = "SELECT a1.answer_id,a1.answer, COUNT(a2.answer_id)AS votes " .
                "FROM (SELECT answer_id, answer from  `oc_answer` WHERE question_id = " . intval($questionId) .
                ")  AS a1 LEFT JOIN (SELECT `vote_id`,`answer_id` FROM `oc_voting` WHERE `question_id` = " . intval($questionId) .
                ") AS a2 on a1.answer_id = a2.answer_id GROUP BY a1.answer_id";
        $answers = mysql_query($queryAnswers, $link) or die('Errant query:  ' . $queryAnswers);
        if (mysql_num_rows($answers)) {
            while ($record = mysql_fetch_assoc($answers)) {
                $records[] = $record;
            }
        }
        echo json_encode(array('result' => 'success', 'answer_id' => $answerId, 'remainingvotes' => '3', 'answers' => $records, 'message' => 'vote added'));
    }
    closeDbLink($link);
});
//--Comments
$app->get('/comments/:id', function($questionId) {
    $link = getDbLink();
    $query = "select comment_id, question_id, comment, posted_user, posted_user_name, posted_date from oc_comments where question_id=" . intval($questionId) . " order by posted_date DESC LIMIT 10";
    $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
    $records = array();
    if (mysql_num_rows($result)) {
        while ($record = mysql_fetch_assoc($result)) {
            //var_dump($record);
            $records[] = array('record' => $record);
        }
    }
    header('Content-type: application/json');
    echo json_encode(array('records' => $records));
    closeDbLink($link);
});
$app->post('/comment', function() {
    $link = getDbLink();
    try {
        begin($link);
        $questionid = isset($_POST['questionid']) ? $_POST['questionid'] : 0;
        $comment = isset($_POST['comment']) ? $_POST['comment'] : 0;
        $userid = isset($_POST['userid']) ? $_POST['userid'] : 0;
        $username = isset($_POST['username']) ? $_POST['username'] : 0;
        
        //insert values into the comments table
        $SQL = " INSERT INTO oc_comments SET ";
        $SQL.= " question_id = '" . intval($questionid) . "',";
        $SQL.= " comment = '" . $comment . "',";
        $SQL.= " posted_user = '" . intval($userid) . "',";
        $SQL.= " posted_user_name = '" . $username . "',";
        $SQL.= " posted_date = NOW() ";
         mysql_query($SQL, $link) or die('Errant query:  ' . $SQL);
        $commentid = mysql_insert_id();
        
        commit($link);
        echo json_encode(array('result' => 'success', "commentid" => $commentid));
    } catch (Exception $e) {
        rollback($link);
        echo json_encode(array('result' => 'failed'));
    }
    closeDbLink($link);   
});
$app->post('/question', function() {
//    session_start();
//    if (!empty($_SESSION['user_id'])) {
//        echo json_encode(array('userid' => $_SESSION['user_id']));
//    } else {
//        echo json_encode(array('userid' => -1));
//    }

    /* 	Output:
     * 		1.QuestionId
     * 	Input:
     * 		1.cat_id AS categoryid
     * 		2.cat_name AS categoryname
     * 		3.question
     * 		4.option(as an array)
     * 		5.userid
     * 		6.who_can_see, <default-0;friends-1>
     * 		7.who_can_answer, <default-0;friends-1>
     * 		8.days_to_decide(num_of_days),
     * 		9.show_my_name(YES/NO) <hide_my_name; 0 or 1>,
     * 		10.default_chart
     */
    $link = getDbLink();
    try {
        begin($link);
        $userid = isset($_POST['userid']) ? $_POST['userid'] : 0;
        $categoryid = isset($_POST['categoryid']) ? $_POST['categoryid'] : 0;
        $categoryname = isset($_POST['categoryname']) ? $_POST['categoryname'] : 0;
        $question = isset($_POST['question']) ? $_POST['question'] : 0;
        $option = isset($_POST['option']) ? $_POST['option'] : 0;
        $optionArray = json_decode($option);

        $whocansee = isset($_POST['whocansee']) ? $_POST['whocansee'] : 0;
        $whocananswer = isset($_POST['whocananswer']) ? $_POST['whocananswer'] : 0;
        $daystodecide = isset($_POST['daystodecide']) ? $_POST['daystodecide'] : 365;
        $showmyname = isset($_POST['showmyname']) ? $_POST['showmyname'] : 0;
        $hidemyname = $showmyname == 1 ? 0 : 1;
        //insert values into the question database
        $query = "INSERT INTO `oc_question`(`customer_id`, `category_id`, `group_id`, `question`, `email_sent`, `who_can_see`, `who_can_answer`, `hide_my_name`, `showmultiplegraph`,`urgency_day` , `recent_activity_date`) VALUES (" . intval($userid) . "," . intval($categoryid) . ",0,'" . $question . "',0," . intval($whocansee) . "," . intval($whocananswer) . "," . intval($hidemyname) . ",0," . intval($daystodecide) . ", NOW())";
        mysql_query($query, $link);
        $questionId = mysql_insert_id();
        //insert options in the answers database			
        for ($i = 0; $i < count($optionArray); ++$i) {
            $query = "INSERT INTO `oc_answer`( `question_id`, `answer`) VALUES (" . intval($questionId) . ", '" . $optionArray[$i] . "')";
            $result = mysql_query($query, $link) or die('Errant query:  ' . $query);
        }
        commit($link);
        echo json_encode(array('result' => 'success', "questionId" => $questionId));
    } catch (Exception $e) {
        rollback($link);
        echo json_encode(array('result' => 'failed'));
    }
    closeDbLink($link);
});





// PUT route
$app->put('/put', function () {
    echo 'This is a PUT route';
}
);

// PATCH route
$app->patch('/patch', function () {
    echo 'This is a PATCH route';
});

// DELETE route
$app->delete('/delete', function () {
    echo 'This is a DELETE route';
}
);

/**
 * Step 4: Run the Slim application
 *
 * This method should be called last. This executes the Slim application
 * and returns the HTTP response to the HTTP client.
 */
$app->run();
